﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using static System.Net.Mime.MediaTypeNames;
using System.Xml.Linq;
using IntelligentGateway.Model.Interface;
using IntelligentGateway.Model.Attribute;
using Dapper.Contrib.Extensions;
using Newtonsoft.Json.Linq;

namespace IntelligentGateway.Tools
{
    public class SqlBuilderTools
    {
        public static string GenarateSqlStr<T>(T t, string PreName = "", bool matching = false, int startindex = -1, int endindex = 0) where T : IBase
        {
            Type type = typeof(T);
            PropertyInfo[] infos = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            StringBuilder sbSql = new StringBuilder();
            string strInnerJoin = string.Empty;
            string strPreInnerJoin = string.Empty;
            string strOrderBy = string.Empty;
            if (t.TotalRecords <= 0 && startindex >= 0)
                sbSql.Append("Select SQL_CALC_FOUND_ROWS ");
            else
                sbSql.Append("Select ");
            foreach (PropertyInfo info in infos)
            {
                IgnoreAttribute ignore = (IgnoreAttribute)info.GetCustomAttribute(typeof(IgnoreAttribute));
                if (ignore != null)
                {
                    if (info.Name.Equals("CustomWhere"))
                    {
                        object where = info.GetValue(t, null);
                        if (where != null && !string.IsNullOrEmpty(where.ToString()))
                            sb.Append(where + " and ");
                    }
                    if (info.Name.Equals("OrderBy"))
                    {
                        if (info.GetValue(t, null) != null && !string.IsNullOrEmpty(info.GetValue(t, null).ToString()))
                        {
                            strOrderBy = "  order by " + info.GetValue(t, null).ToString() + " ";
                        }

                    }
                    continue;
                }
                ForeignRelAttribute foreignRel = (ForeignRelAttribute)info.GetCustomAttribute(typeof(ForeignRelAttribute));
                if (foreignRel != null)
                {
                    string strJoinType = string.IsNullOrEmpty(foreignRel.JoinType) ? "  inner " : foreignRel.JoinType;
                    string joinTable = foreignRel.RelTable;
                    if (foreignRel.RelTable != string.Empty)
                    {
                        if (foreignRel.RelTable.Contains("as"))
                        {
                            int index = foreignRel.RelTable.IndexOf("as");
                            joinTable = foreignRel.RelTable.Substring(index + 2);
                        }
                        if (foreignRel.RelTable.Equals(strPreInnerJoin))
                        {
                            string infoName = info.Name;
                            if (infoName.Contains("_"))
                            {
                                infoName = infoName.Replace("_", ".");
                                strInnerJoin += ("  and  " +
                                         infoName + " = " + joinTable + "." + foreignRel.RelColumn);

                            }
                            else
                            {
                                strInnerJoin += ("  and  " +
                                ((string.IsNullOrEmpty(foreignRel.JoinTable)) ? type.Name : foreignRel.JoinTable) + "." + info.Name + " = " + joinTable + "." + foreignRel.RelColumn);
                            }
                        }
                        else strInnerJoin += ("   " + strJoinType + " join " + foreignRel.RelTable + " on " +
                            ((string.IsNullOrEmpty(foreignRel.JoinTable)) ? type.Name : foreignRel.JoinTable) + "." + info.Name + " = " + joinTable + "." + foreignRel.RelColumn);

                        strPreInnerJoin = foreignRel.RelTable;
                    }

                }
                NoSelectAttribute noSelectAttribute = (NoSelectAttribute)info.GetCustomAttribute(typeof(NoSelectAttribute));
                if (noSelectAttribute != null)
                {
                    continue;
                }
                FSelectAttribute fSelect = (FSelectAttribute)info.GetCustomAttribute(typeof(FSelectAttribute));
                if (fSelect != null)
                {
                    sbSql.Append(fSelect.TableName + "." + info.Name);
                    //sbSql.Append(",");                    
                }
                else if (foreignRel != null && foreignRel.JoinTable != string.Empty)
                {

                    sbSql.Append(foreignRel.JoinTable + "." + info.Name);
                }
                else sbSql.Append(type.Name + "." + info.Name);
                sbSql.Append(",");
                object value = info.GetValue(t, null);
                if (value != null && !string.IsNullOrEmpty(value.ToString()))
                {
                    string name = string.Empty;
                    if (foreignRel != null && foreignRel.JoinTable != string.Empty)
                    {
                        name = foreignRel.JoinTable + "." + info.Name;
                    }
                    else if (fSelect != null)
                    {
                        name = fSelect.TableName + "." + info.Name;
                    }
                    else name = type.Name + "." + info.Name;
                    if (info.PropertyType.ToString().Contains("DateTime"))
                    {
                        if (DateTime.Parse(value.ToString()).Year == 1)
                            continue;
                        if (name.Contains("BegDate"))
                        {
                            sb.Append(" BillDate >= '" + value + "' and ");
                        }
                        else if (name.Contains("EndDate"))
                        {
                            sb.Append(" BillDate <= '" + value + "' and ");
                        }
                        else
                        {
                            sb.Append(name + "='" + value + "' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("String"))
                    {
                        if (matching)
                            sb.Append(name + " = '" + value + "' and ");
                        else
                        {
                            sb.Append(name + " like '%" + value + "%' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("Nullable"))
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Boolean"))
                        {

                            if (value.ToString().ToLower().Contains("false"))
                            {
                                value = "0";
                            }
                            else if (value.ToString().ToLower().Contains("true"))
                            {
                                value = "1";
                            }
                            else continue;
                        }
                        //    continue;
                        sb.Append(name + "=" + value + " and ");
                    }
                    else
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;

                        sb.Append(name + "=" + value + " and ");

                    }
                }
            }
            string strSql = sbSql.ToString().Substring(0, sbSql.Length - 1);
            strSql = strSql + " from " + type.Name + strInnerJoin;
            string strFilter = sb.ToString();

            if (strFilter.Equals(""))
            {
                strSql = strSql + strOrderBy;
                strSql = strSql + (startindex >= 0 ? " LIMIT " + startindex + "," + endindex : "");
            }
            else
                strSql = strSql + " Where (" + strFilter.Substring(0, strFilter.Length - 4) + ")" + strOrderBy + (startindex >= 0 ? " LIMIT " + startindex + "," + endindex : "");
            if (t.TotalRecords <= 0 && startindex >= 0)
                strSql = strSql + " ; SELECT FOUND_ROWS() total;";
            return strSql;
        }
        public static string GenarateTotalRecordsStr<T>(T t, string PreName = "", bool matching = false)
        {
            Type type = typeof(T);
            PropertyInfo[] infos = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            StringBuilder sbSql = new StringBuilder();
            string strInnerJoin = string.Empty;
            string strPreInnerJoin = string.Empty;
            sbSql.Append("Select ");
            foreach (PropertyInfo info in infos)
            {
                IgnoreAttribute ignore = (IgnoreAttribute)info.GetCustomAttribute(typeof(IgnoreAttribute));
                if (ignore != null)
                {
                    if (info.Name.Equals("CustomWhere"))
                    {
                        object where = info.GetValue(t, null);
                        if (where != null && !string.IsNullOrEmpty(where.ToString()))
                            sb.Append(where + " and ");
                    }
                    continue;
                }
                ForeignRelAttribute foreignRel = (ForeignRelAttribute)info.GetCustomAttribute(typeof(ForeignRelAttribute));
                if (foreignRel != null)
                {
                    string strJoinType = string.IsNullOrEmpty(foreignRel.JoinType) ? "  inner " : foreignRel.JoinType;
                    string joinTable = foreignRel.RelTable;
                    if (foreignRel.RelTable != string.Empty)
                    {
                        if (foreignRel.RelTable.Contains("as"))
                        {
                            int index = foreignRel.RelTable.IndexOf("as");
                            joinTable = foreignRel.RelTable.Substring(index + 2);
                        }
                        if (foreignRel.RelTable.Equals(strPreInnerJoin))
                        {
                            string infoName = info.Name;
                            if (infoName.Contains("_"))
                            {
                                infoName = infoName.Replace("_", ".");
                                strInnerJoin += ("  and  " +
                                         infoName + " = " + joinTable + "." + foreignRel.RelColumn);

                            }
                            else
                            {
                                strInnerJoin += ("  and  " +
                                ((string.IsNullOrEmpty(foreignRel.JoinTable)) ? type.Name : foreignRel.JoinTable) + "." + info.Name + " = " + joinTable + "." + foreignRel.RelColumn);
                            }
                        }
                        else strInnerJoin += ("   " + strJoinType + " join " + foreignRel.RelTable + " on " +
                            ((string.IsNullOrEmpty(foreignRel.JoinTable)) ? type.Name : foreignRel.JoinTable) + "." + info.Name + " = " + joinTable + "." + foreignRel.RelColumn);

                        strPreInnerJoin = foreignRel.RelTable;
                    }

                }
                NoSelectAttribute noSelectAttribute = (NoSelectAttribute)info.GetCustomAttribute(typeof(NoSelectAttribute));
                if (noSelectAttribute != null)
                {
                    continue;
                }
                FSelectAttribute fSelect = (FSelectAttribute)info.GetCustomAttribute(typeof(FSelectAttribute));
                if (fSelect != null)
                {
                    sbSql.Append(fSelect.TableName + "." + info.Name);
                    //sbSql.Append(",");                    
                }
                else if (foreignRel != null && foreignRel.JoinTable != string.Empty)
                {

                    sbSql.Append(foreignRel.JoinTable + "." + info.Name);
                }
                else sbSql.Append(type.Name + "." + info.Name);
                sbSql.Append(",");
                object value = info.GetValue(t, null);
                if (value != null && !string.IsNullOrEmpty(value.ToString()))
                {
                    string name = string.Empty;
                    if (foreignRel != null && foreignRel.JoinTable != string.Empty)
                    {
                        name = foreignRel.JoinTable + "." + info.Name;
                    }
                    else if (fSelect != null)
                    {
                        name = fSelect.TableName + "." + info.Name;
                    }
                    else name = type.Name + "." + info.Name;
                    if (info.PropertyType.ToString().Contains("DateTime"))
                    {
                        if (DateTime.Parse(value.ToString()).Year == 1)
                            continue;
                        if (name.Contains("BegDate"))
                        {
                            sb.Append(" BillDate >= '" + value + "' and ");
                        }
                        else if (name.Contains("EndDate"))
                        {
                            sb.Append(" BillDate <= '" + value + "' and ");
                        }
                        else
                        {
                            sb.Append(name + "='" + value + "' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("String"))
                    {
                        if (matching)
                            sb.Append(name + " = '" + value + "' and ");
                        else
                        {
                            sb.Append(name + " like '%" + value + "%' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("Nullable"))
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Boolean"))
                        {

                            if (value.ToString().ToLower().Contains("false"))
                            {
                                value = "0";
                            }
                            else if (value.ToString().ToLower().Contains("true"))
                            {
                                value = "1";
                            }
                            else continue;
                        }
                        //    continue;
                        sb.Append(name + "=" + value + " and ");
                    }
                    else
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;

                        sb.Append(name + "=" + value + " and ");

                    }
                }
            }

            string strSql = "SELECT COUNT(1) FROM " + type.Name + strInnerJoin;
            string strFilter = sb.ToString();

            if (strFilter.Equals(""))
            {
                return strSql;
            }
            else
                return strSql + " Where (" + strFilter.Substring(0, strFilter.Length - 4) + ")";
        }

        public static string GenarateUpdateStr<T>(T t) where T : class
        {
            Type type = typeof(T);
            PropertyInfo[] infos = type.GetProperties();
            StringBuilder sbSql = new StringBuilder();
            List<string> updateWhere = new List<string>();
            sbSql.Append("Update " + type.Name + " Set ");
            foreach (PropertyInfo info in infos)
            {
                object value = info.GetValue(t, null);
                KeyAttribute key = (KeyAttribute)info.GetCustomAttribute(typeof(KeyAttribute));
                if (key != null)
                {
                    if (value != null && int.Parse(value.ToString()) > 0)
                        updateWhere.Add(info.Name + " = " + value);
                    continue;
                }
                else
                {
                    ExplicitKeyAttribute explicitKey = (ExplicitKeyAttribute)info.GetCustomAttribute(typeof(ExplicitKeyAttribute));
                    if (explicitKey != null && int.Parse(value.ToString()) > 0)
                    {
                        if (value != null)
                            updateWhere.Add(info.Name + " = " + value);
                        continue;
                    }
                }
                IgnoreAttribute ignore = (IgnoreAttribute)info.GetCustomAttribute(typeof(IgnoreAttribute));
                if (ignore != null)
                {
                    continue;
                }
                WriteAttribute writeAttribute = (WriteAttribute)info.GetCustomAttribute(typeof(WriteAttribute));
                if (writeAttribute != null)
                {
                    continue;
                }
                if (value != null)
                {
                    if (info.PropertyType.ToString().Contains("DateTime"))
                    {
                        if (DateTime.Parse(value.ToString()).Year == 1)
                            continue;
                        sbSql.Append(info.Name + "='" + value + "',");
                    }
                    else if (info.PropertyType.Name.Contains("String"))
                    {
                        sbSql.Append(info.Name + "='" + value + "',");
                    }
                    else if (int.Parse(value.ToString()) > 0)
                    {
                        sbSql.Append(info.Name + "=" + value + ",");
                    }
                    else if (int.Parse(value.ToString()) == 0 && info.PropertyType.Name.Contains("Nullable`1"))
                    {
                        sbSql.Append(info.Name + "=" + value + ",");
                    }
                }
            }
            if (updateWhere.Count <= 0) return "";
            StringBuilder sbWhere = new StringBuilder();
            foreach (string str in updateWhere)
            {
                sbWhere.Append(str);
                sbWhere.Append(" and ");
            }
            string sql = sbSql.ToString().Substring(0, sbSql.Length - 1);
            string sqlWhere = sbWhere.ToString().Substring(0, sbWhere.Length - 4);
            return sql + " Where " + sqlWhere;
        }
        public static string GenarateDeleteStr<T>(T t) where T : class
        {
            Type type = typeof(T);
            PropertyInfo[] infos = type.GetProperties();
            StringBuilder sbSql = new StringBuilder();
            List<string> deleteWhere = new List<string>();
            sbSql.Append("Delete from " + type.Name + " ");
            foreach (PropertyInfo info in infos)
            {
                object value = info.GetValue(t, null);
                KeyAttribute key = (KeyAttribute)info.GetCustomAttribute(typeof(KeyAttribute));
                if (key != null)
                {
                    if (value != null && int.Parse(value.ToString()) > 0)
                        deleteWhere.Add(info.Name + " = " + value);
                    continue;
                }
                else
                {
                    ExplicitKeyAttribute explicitKey = (ExplicitKeyAttribute)info.GetCustomAttribute(typeof(ExplicitKeyAttribute));
                    if (explicitKey != null && int.Parse(value.ToString()) > 0)
                    {
                        if (value != null)
                            deleteWhere.Add(info.Name + " = " + value);
                        continue;
                    }
                }
                IgnoreAttribute ignore = (IgnoreAttribute)info.GetCustomAttribute(typeof(IgnoreAttribute));
                if (ignore != null)
                {
                    continue;
                }
                if (value != null)
                {
                    if (info.PropertyType.ToString().Contains("DateTime"))
                    {
                        if (DateTime.Parse(value.ToString()).Year == 1)
                            continue;
                        deleteWhere.Add(info.Name + "='" + value + "' ");
                    }
                    else if (info.PropertyType.Name.Contains("String"))
                    {
                        deleteWhere.Add(info.Name + "='" + value + "' ");
                    }
                    else if (int.Parse(value.ToString()) > 0)
                    {
                        deleteWhere.Add(info.Name + "=" + value + " ");
                    }
                }
            }
            if (deleteWhere.Count <= 0) return "";
            StringBuilder sbWhere = new StringBuilder();
            foreach (string str in deleteWhere)
            {
                sbWhere.Append(str);
                sbWhere.Append(" and ");
            }
            string sql = sbSql.ToString().Substring(0, sbSql.Length - 1);
            string sqlWhere = sbWhere.ToString().Substring(0, sbWhere.Length - 4);
            return sql + " Where " + sqlWhere;
        }
        public static string GenarateWhereStr(string StrJson)
        {
            JToken o1 = JObject.Parse(StrJson);
            StringBuilder sb = new StringBuilder();
            string page = string.Empty;
            string orderby = string.Empty;
            string customewhere = string.Empty;
            foreach (JProperty p in o1)
            {
                if (p.Name.ToLower().Equals("page"))
                {
                    string[] spit = p.Value.ToString().Split(',');
                    if (spit.Length == 2)
                    {
                        page = " limit " + spit[0] + "," + spit[1];
                    }
                    continue;
                }
                if (p.Name.ToLower().Equals("orderby"))
                {
                    orderby = "order by " + p.Value.ToString();
                    continue;
                }
                if (p.Name.ToLower().Equals("customwhere"))
                {
                    customewhere = p.Value.ToString();
                    continue;
                }
                if (!p.Value.ToString().Equals(""))
                {
                    sb.Append(p.Name);
                    if (p.Value.Type == JTokenType.String)
                    {
                        if (p.Value.ToString().Contains("in("))
                        {
                            sb.Append(" " + p.Value + " ");
                        }
                        else
                        {
                            sb.Append(" like '%");
                            sb.Append(p.Value);
                            sb.Append("%'");
                        }
                    }
                    else
                    {

                        sb.Append(" = ");
                        sb.Append(p.Value);

                    }
                    sb.Append(" and ");
                }
            }
            string strFilter = sb.ToString();
            if (strFilter.Length > 0)
            {
                strFilter = " Where " + strFilter.Substring(0, strFilter.Length - 5);
            }
            strFilter = strFilter + "^" + page + "^" + customewhere + "^" + orderby;
            /*if (page!=string.Empty)
            {
                strFilter = strFilter + "^" + page;
            }
            if(orderby!=string.Empty)
            {
                strFilter = strFilter + "^" + orderby;
            }
            if(customewhere!=string.Empty)
            {
                strFilter = strFilter + "^" + customewhere;
            }*/
            return strFilter;
        }
        public static string GenarateWhereSql<T>(T t, string PreName = "", bool matching = false, int startindex = -1, int endindex = 0) where T : IBase
        {
            Type type = typeof(T);
            PropertyInfo[] infos = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            foreach (PropertyInfo info in infos)
            {
                IgnoreAttribute ignore = (IgnoreAttribute)info.GetCustomAttribute(typeof(IgnoreAttribute));
                if (ignore != null)
                {
                    continue;
                }
                object value = info.GetValue(t, null);
                if (value != null && !string.IsNullOrEmpty(value.ToString()))
                {
                    string name = type.Name + "." + info.Name;
                    if (info.PropertyType.ToString().Contains("DateTime"))
                    {
                        if (DateTime.Parse(value.ToString()).Year == 1)
                            continue;
                        if (name.Contains("BegDate"))
                        {
                            sb.Append(" BillDate >= '" + value + "' and ");
                        }
                        else if (name.Contains("EndDate"))
                        {
                            sb.Append(" BillDate <= '" + value + "' and ");
                        }
                        else
                        {
                            sb.Append(name + "='" + value + "' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("String"))
                    {
                        if (matching)
                            sb.Append(name + " = '" + value + "' and ");
                        else
                        {
                            sb.Append(name + " like '%" + value + "%' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("Nullable"))
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Boolean"))
                        {

                            if (value.ToString().ToLower().Contains("false"))
                            {
                                value = "0";
                            }
                            else if (value.ToString().ToLower().Contains("true"))
                            {
                                value = "1";
                            }
                            else continue;
                        }
                        //    continue;
                        sb.Append(name + "=" + value + " and ");
                    }
                    else
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;

                        sb.Append(name + "=" + value + " and ");

                    }
                }
            }
            string strFilter = sb.ToString();

            string strSql = " Where (" + strFilter.Substring(0, strFilter.Length - 4) + ")" + (startindex >= 0 ? " LIMIT " + startindex + "," + endindex : "");

            return strSql;
        }
    }
}
